package com.rensanning.poi;

import java.io.File;

import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Footer;
import org.apache.poi.ss.usermodel.Header;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class T2_Worksheet extends AbstractSample {

	private static String FILE_DIR = "C:\\Users\\Administrator\\Desktop\\";

	private static final String TEST_WORKSHEET_NAME = FILE_DIR + "sample_worksheet.xls";

	public static void main(String[] args) {
		
		File f = new File(FILE_DIR);
		if (!f.isDirectory()) {
			f.mkdirs();
		}
		
		T2_Worksheet s = new T2_Worksheet();

		//生成Sheet
		s.createSheet();
		
		//Sheet缩放
		s.zoomSheet();
		
		//行列分组
		s.groupSheet();
		
		//关闭分组
		s.collapseGroup();
		
		//插入行
		s.addRow();
		
		//删除行
		s.deleteRow();
		
		//移动行
		s.moveRow();
		
		//修改行高
		s.setRowHeight();
		
		//修改列宽
		s.setColumnWidth();
		
		//不显示网格线
		s.setGridlines();
		
		//设置分页
		s.setPaging();
		
		//添加，删除，合并单元格
		s.operageCell();
		
		//设置Header，Footer
		s.setHeaderFooter();
		
	}
	
	public void createSheet() {
		HSSFWorkbook wb = new HSSFWorkbook();		
		for (int i = 0; i < 20; i++) {
			wb.createSheet("sheetname" + (i+1));
		}		
		createExcelFile(wb, TEST_WORKSHEET_NAME);
	}
	
	public void zoomSheet() {
		Workbook wb = readExcelFile(TEST_WORKSHEET_NAME);
		
		//setZoom(int numerator, int denominator)
		//"numerator"÷"denominator"  例如： 3÷1＝3 那就是设置为300%
		
		//扩大(200%)
		wb.getSheet("sheetname1").setZoom(2, 1);
		//缩小(50%)
		wb.getSheet("sheetname2").setZoom(1, 2);
		
		createExcelFile(wb, TEST_WORKSHEET_NAME);
	}
	
	public void groupSheet() {
		Workbook wb = readExcelFile(TEST_WORKSHEET_NAME);
		
		wb.getSheet("sheetname3").groupColumn(4, 7);
		wb.getSheet("sheetname3").groupColumn(9, 12);
		wb.getSheet("sheetname3").groupColumn(10, 11);
		
		wb.getSheet("sheetname3").groupRow(5, 14);
		wb.getSheet("sheetname3").groupRow(7, 13);
		wb.getSheet("sheetname3").groupRow(16, 19);
		
		createExcelFile(wb, TEST_WORKSHEET_NAME);
	}
	
	public void collapseGroup() {
		Workbook wb = readExcelFile(TEST_WORKSHEET_NAME);
		
		wb.getSheet("sheetname3").setColumnGroupCollapsed(10, true);
		wb.getSheet("sheetname3").setRowGroupCollapsed(7, true);
		
		createExcelFile(wb, TEST_WORKSHEET_NAME);
	}
	
	public void addRow() {
		Workbook wb = readExcelFile(TEST_WORKSHEET_NAME);
		
		Row row1 = wb.getSheet("sheetname4").createRow(1);
	    Cell cell1_1 = row1.createCell(1);
	    cell1_1.setCellValue(123);

	    Row row4 = wb.getSheet("sheetname4").createRow(4);
	    Cell cell4_3 = row4.createCell(3);
	    cell4_3.setCellValue("中国");
		
		createExcelFile(wb, TEST_WORKSHEET_NAME);
	}
	
	public void deleteRow() {
		Workbook wb = readExcelFile(TEST_WORKSHEET_NAME);
		
	    Row row = wb.getSheet("sheetname4").getRow(1);
	    wb.getSheet("sheetname4").removeRow(row);
		
		createExcelFile(wb, TEST_WORKSHEET_NAME);
	}
	
	public void moveRow() {
		Workbook wb = readExcelFile(TEST_WORKSHEET_NAME);
		
		//追加行
	    for (int i = 0; i < 10; i++) {
		    Row row = wb.getSheet("sheetname5").createRow(i);
		    Cell cell = row.createCell(0);
		    cell.setCellValue(i + 1);
	    }

	    //******移动行只移动内容，不牵扯行的删除和插入
	    
	    //移动行（把第1行和第2行移到第5行之后）
	    wb.getSheet("sheetname5").shiftRows(0, 1, 5);

	    //移动行（把第3行和第4行往上移动1行）
	    wb.getSheet("sheetname5").shiftRows(2, 3, -1);
		
		createExcelFile(wb, TEST_WORKSHEET_NAME);
	}

	public void setRowHeight() {
		Workbook wb = readExcelFile(TEST_WORKSHEET_NAME);
		
		//追加行
	    for (int i = 0; i < 10; i++) {
		    Row row = wb.getSheet("sheetname6").createRow(i);
		    Cell cell = row.createCell(0);
		    cell.setCellValue(i + 1);
	    }
		    
	    //设置默认行高
		wb.getSheet("sheetname6").setDefaultRowHeight((short)100);

	    //设置行高
	    wb.getSheet("sheetname6").getRow(2).setHeight((short)(100 * 20));
		
		createExcelFile(wb, TEST_WORKSHEET_NAME);
	}

	public void setColumnWidth() {
		Workbook wb = readExcelFile(TEST_WORKSHEET_NAME);
		
		//设置默认列宽
		wb.getSheet("sheetname7").setDefaultColumnWidth(12);

	    //设置列宽
	    wb.getSheet("sheetname7").setColumnWidth(0, 5 * 256);
		
		createExcelFile(wb, TEST_WORKSHEET_NAME);
	}
	
	public void setGridlines() {
		Workbook wb = readExcelFile(TEST_WORKSHEET_NAME);
		
	    //不显示网格线
		wb.getSheet("sheetname8").setDisplayGridlines(false);
		
		createExcelFile(wb, TEST_WORKSHEET_NAME);
	}
	
	public void setPaging() {
		Workbook wb = readExcelFile(TEST_WORKSHEET_NAME);
		
		//追加行
	    for (int i = 0; i < 10; i++) {
		    Row row = wb.getSheet("sheetname9").createRow(i);
		    for (int j = 0; j < 10; j++) {
			    Cell cell = row.createCell(j);
			    cell.setCellValue(i + 1);
		    }
	    }
	    
	    //设置第一页：3行2列 （可以多次设置）
		wb.getSheet("sheetname9").setRowBreak(2);
		wb.getSheet("sheetname9").setColumnBreak(1);
		
		createExcelFile(wb, TEST_WORKSHEET_NAME);
	}
	
	public void operageCell() {
		Workbook wb = readExcelFile(TEST_WORKSHEET_NAME);
		
		//追加行
	    for (int i = 0; i < 10; i++) {
		    Row row = wb.getSheet("sheetname10").createRow(i);
		    for (int j = 0; j < 10; j++) {
		    	//添加单元格
			    Cell cell = row.createCell(j);
			    cell.setCellValue(i + 1);
		    }
		    
		    //删除单元格
		    row.removeCell(row.getCell(5));
	    }
	    
	    //合并单元格
	    //CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
	    wb.getSheet("sheetname10").addMergedRegion(new CellRangeAddress(1, 4, 2, 3));
		
		createExcelFile(wb, TEST_WORKSHEET_NAME);
	}

	public void setHeaderFooter() {
		Workbook wb = readExcelFile(TEST_WORKSHEET_NAME);
		
		//Header
        Header header = wb.getSheet("sheetname11").getHeader();
        header.setLeft(HSSFHeader.startUnderline() +
                HSSFHeader.font("宋体", "Italic") +
                "文字文字" +
                HSSFHeader.endUnderline());
        header.setCenter(HSSFHeader.fontSize((short)16) +
                HSSFHeader.startDoubleUnderline() +
                HSSFHeader.startBold() +
                "汉字汉字" +
                HSSFHeader.endBold() +
                HSSFHeader.endDoubleUnderline());
        header.setRight("打印时间:" + HSSFHeader.date() +  " " + HSSFHeader.time());
        
        //Footer
        Footer footer = wb.getSheet("sheetname11").getFooter();
        footer.setLeft("Copyright @ rensanning");
        footer.setCenter("Page:" + HSSFFooter.page() + " / " + HSSFFooter.numPages());
        footer.setRight("File:" + HSSFFooter.file());
        
		createExcelFile(wb, TEST_WORKSHEET_NAME);
	}

}
